﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IPR_StudyMeetingMrgSys.DBUtility;//Please add references
namespace IPR_StudyMeetingMrgSys.DAL
{
	/// <summary>
	/// 数据访问类:TrainPlan
	/// </summary>
	public partial class TrainPlan
	{
		public TrainPlan()
		{}
		#region  BasicMethod



		/// <summary>
		/// 增加一条数据
		/// </summary>
		public long Add(IPR_StudyMeetingMrgSys.Model.TrainPlan model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into TrainPlan(");
			strSql.Append("TPID,TPName,TPDescribe,TPIsRelease,TPReleaseStart,TPReleaseEnd,TPDesignUserID,TPDesignUserName,TPAddUserID,TPAddUserName,TPAddDateTime,TPDepID,CPID,IsUse,TPDepLevel,IsCheck,TPPID,Remarks,AnnexName,AnnexUrl,TPDescribeContent)");//,TPSendDepID
            strSql.Append(" values (");
			strSql.Append("@TPID,@TPName,@TPDescribe,@TPIsRelease,@TPReleaseStart,@TPReleaseEnd,@TPDesignUserID,@TPDesignUserName,@TPAddUserID,@TPAddUserName,@TPAddDateTime,@TPDepID,@CPID,@IsUse,@TPDepLevel,@IsCheck,@TPPID,@Remarks,@AnnexName,@AnnexUrl,@TPDescribeContent)");//,@TPSendDepID

            strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@TPID", SqlDbType.BigInt,8),
					new SqlParameter("@TPName", SqlDbType.NVarChar,255),
					new SqlParameter("@TPDescribe", SqlDbType.NVarChar,2000),
					new SqlParameter("@TPIsRelease", SqlDbType.Int,4),
					new SqlParameter("@TPReleaseStart", SqlDbType.DateTime),
					new SqlParameter("@TPReleaseEnd", SqlDbType.DateTime),
					new SqlParameter("@TPDesignUserID", SqlDbType.NVarChar,20),
					new SqlParameter("@TPDesignUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@TPAddUserID", SqlDbType.NVarChar,20),
					new SqlParameter("@TPAddUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@TPAddDateTime", SqlDbType.DateTime),
					new SqlParameter("@TPDepID", SqlDbType.Int,4),
					new SqlParameter("@CPID", SqlDbType.BigInt,8),
					new SqlParameter("@IsUse", SqlDbType.Int,4),
					new SqlParameter("@TPDepLevel", SqlDbType.Int,4),
                    new SqlParameter("@IsCheck", SqlDbType.Int,4),
                    new SqlParameter("@TPPID", SqlDbType.BigInt,8),
                //new SqlParameter("@TPSendDepID", SqlDbType.NVarChar,300)
                new SqlParameter("@Remarks", SqlDbType.NVarChar,300),
                new SqlParameter("@AnnexName", SqlDbType.NVarChar,100),
                new SqlParameter("@AnnexUrl", SqlDbType.NVarChar,200),
                new SqlParameter("@TPDescribeContent",SqlDbType.NVarChar,2000)
            };
			parameters[0].Value = model.TPID;
			parameters[1].Value = model.TPName;
			parameters[2].Value = model.TPDescribe;
			parameters[3].Value = model.TPIsRelease;
			parameters[4].Value = model.TPReleaseStart;
			parameters[5].Value = model.TPReleaseEnd;
			parameters[6].Value = model.TPDesignUserID;
			parameters[7].Value = model.TPDesignUserName;
			parameters[8].Value = model.TPAddUserID;
			parameters[9].Value = model.TPAddUserName;
			parameters[10].Value = model.TPAddDateTime;
			parameters[11].Value = model.TPDepID;
			parameters[12].Value = model.CPID;
			parameters[13].Value = model.IsUse;
            parameters[14].Value = model.TPDepLevel;
            parameters[15].Value = model.IsCheck;
            parameters[16].Value = model.TPPID;
            //parameters[17].Value = model.TPSendDepID;
            parameters[17].Value = model.Remarks;
            parameters[18].Value = model.AnnexName;
            parameters[19].Value = model.AnnexUrl;
            parameters[20].Value = model.TPDescribeContent;

            
            object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt64(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(IPR_StudyMeetingMrgSys.Model.TrainPlan model,out string errorMsg)
		{
            bool isContinue = true; errorMsg = string.Empty;
            IPR_StudyMeetingMrgSys.Model.TrainPlan obj = new IPR_StudyMeetingMrgSys.Model.TrainPlan();
            StringBuilder strSql_select = new StringBuilder();
            strSql_select.Append("select  top 1 TPIsRelease,IsCheck from TrainPlan ");
            strSql_select.Append(" where TPID=@TPID");
            SqlParameter[] parameters_select = {
                    new SqlParameter("@TPID", SqlDbType.BigInt)
            };
            parameters_select[0].Value = model.TPID;
            DataSet ds = DbHelperSQL.Query(strSql_select.ToString(), parameters_select);
            if (ds.Tables[0].Rows.Count > 0)
            {
                var item = ds.Tables[0].Rows[0];
                //if (item["TPIsRelease"] != null && item["TPIsRelease"].ToString() != "")
                //{
                //     if (int.Parse(item["TPIsRelease"].ToString()) > 0)
                //    {
                //        isContinue = false; errorMsg = "已发布不能编辑";
                //    }
                //}
                //if (item["TPIsRelease"] != null && item["TPIsRelease"].ToString() != "")
                //{
                //    if (int.Parse(item["IsCheck"].ToString()) > 0)
                //    {
                //        isContinue = false; errorMsg = "已审核不能编辑";
                //    }
                //}
            }
            if (isContinue)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update TrainPlan set ");
                strSql.Append("TPID=@TPID,");
                strSql.Append("TPName=@TPName,");
                strSql.Append("TPDescribe=@TPDescribe,");
                strSql.Append("TPReleaseStart=@TPReleaseStart,");
                strSql.Append("TPReleaseEnd=@TPReleaseEnd,");
                strSql.Append("CPID=@CPID,");
                strSql.Append("TPPID=@TPPID,");
                strSql.Append("TPSendDepID=@TPSendDepID,");
                strSql.Append("Remarks=@Remarks,");
                strSql.Append("TPDescribeContent=@TPDescribeContent ");
                strSql.Append(" where TPID=@TPID");
                SqlParameter[] parameters = {
                    new SqlParameter("@TPID", SqlDbType.BigInt,8),
                    new SqlParameter("@TPName", SqlDbType.NVarChar,255),
                    new SqlParameter("@TPDescribe", SqlDbType.NVarChar,2000),
                    new SqlParameter("@TPReleaseStart", SqlDbType.DateTime),
                    new SqlParameter("@TPReleaseEnd", SqlDbType.DateTime),
                      new SqlParameter("@CPID", SqlDbType.BigInt,8),
                      new SqlParameter("@TPPID", SqlDbType.BigInt,8)
                      ,new SqlParameter("@TPSendDepID", SqlDbType.NVarChar,300)
                    ,new SqlParameter("@Remarks", SqlDbType.NVarChar,300),
                      new SqlParameter("@TPDescribeContent", SqlDbType.NVarChar,2000),
                };
                parameters[0].Value = model.TPID;
                parameters[1].Value = model.TPName;
                parameters[2].Value = model.TPDescribe;
                parameters[3].Value = model.TPReleaseStart;
                parameters[4].Value = model.TPReleaseEnd;
                parameters[5].Value = model.CPID;
                parameters[6].Value = model.TPPID;
                parameters[7].Value = model.TPSendDepID;
                parameters[8].Value = model.Remarks;
                parameters[9].Value = model.TPDescribeContent;
                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                isContinue = rows > 0 ? true : false;
            }
            return isContinue;
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(long AutoID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append(" update  TrainPlan set IsUse=0 ");
			strSql.Append(" where AutoID=@AutoID");
			SqlParameter[] parameters = {
					new SqlParameter("@AutoID", SqlDbType.BigInt)
			};
			parameters[0].Value = AutoID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public int DeleteList(string AutoIDlist)
		{
			StringBuilder strSql=new StringBuilder();

            var listid = "0";
            StringBuilder strSql_select = new StringBuilder();
            strSql_select.Append("select  top 1 TPID from TrainPlan ");
            strSql_select.Append(" where TPID in (" + AutoIDlist + ") and TPIsRelease='0' and IsCheck='0' ");
            DataSet ds = DbHelperSQL.Query(strSql_select.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var item = ds.Tables[0].Rows[i];
                    if (item["TPID"] != null && item["TPID"].ToString() != "")
                    {
                        if (int.Parse(item["TPID"].ToString()) > 0)
                        {
                            listid += "," + item["TPID"].ToString() + "";
                        }
                    }
                }
            }

            strSql.Append(" update  TrainPlan set IsUse=0  ");
			strSql.Append(" where TPID in ("+ listid + ")  ");
		    return	DbHelperSQL.ExecuteSql(strSql.ToString());
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.TrainPlan GetModel(long TPID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 AutoID,TPID,TPName,TPDescribe,TPIsRelease,TPReleaseStart,TPReleaseEnd,TPDesignUserID,TPDesignUserName,TPAddUserID,TPAddUserName,TPAddDateTime,TPDepID,CPID,IsUse,TPDepLevel,IsCheck,TPPID,Remarks,TPDescribeContent ,AnnexUrl,AnnexName from TrainPlan ");//,TPSendDepID 
            strSql.Append(" where TPID=@TPID");
			SqlParameter[] parameters = {
					new SqlParameter("@TPID", SqlDbType.BigInt)
			};
			parameters[0].Value = TPID;

			IPR_StudyMeetingMrgSys.Model.TrainPlan model=new IPR_StudyMeetingMrgSys.Model.TrainPlan();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public IPR_StudyMeetingMrgSys.Model.TrainPlan DataRowToModel(DataRow row)
		{
			IPR_StudyMeetingMrgSys.Model.TrainPlan model=new IPR_StudyMeetingMrgSys.Model.TrainPlan();
			if (row != null)
			{
				if(row["AutoID"]!=null && row["AutoID"].ToString()!="")
				{
					model.AutoID=long.Parse(row["AutoID"].ToString());
				}
				if(row["TPID"]!=null && row["TPID"].ToString()!="")
				{
					model.TPID=long.Parse(row["TPID"].ToString());
				}
				if(row["TPName"]!=null)
				{
					model.TPName=row["TPName"].ToString();
				}
				if(row["TPDescribe"]!=null)
				{
					model.TPDescribe=row["TPDescribe"].ToString();
				}
				if(row["TPIsRelease"]!=null && row["TPIsRelease"].ToString()!="")
				{
					model.TPIsRelease=int.Parse(row["TPIsRelease"].ToString());
				}
				if(row["TPReleaseStart"]!=null && row["TPReleaseStart"].ToString()!="")
				{
					model.TPReleaseStart=DateTime.Parse(row["TPReleaseStart"].ToString());
				}
				if(row["TPReleaseEnd"]!=null && row["TPReleaseEnd"].ToString()!="")
				{
					model.TPReleaseEnd=DateTime.Parse(row["TPReleaseEnd"].ToString());
				}
				if(row["TPDesignUserID"]!=null && row["TPDesignUserID"].ToString()!="")
				{
					model.TPDesignUserID=row["TPDesignUserID"].ToString();
				}
				if(row["TPDesignUserName"]!=null)
				{
					model.TPDesignUserName=row["TPDesignUserName"].ToString();
				}
				if(row["TPAddUserID"]!=null && row["TPAddUserID"].ToString()!="")
				{
					model.TPAddUserID=row["TPAddUserID"].ToString();
				}
				if(row["TPAddUserName"]!=null)
				{
					model.TPAddUserName=row["TPAddUserName"].ToString();
				}
				if(row["TPAddDateTime"]!=null && row["TPAddDateTime"].ToString()!="")
				{
					model.TPAddDateTime=DateTime.Parse(row["TPAddDateTime"].ToString());
				}
				if(row["TPDepID"]!=null && row["TPDepID"].ToString() != "")
				{
					model.TPDepID=int.Parse( row["TPDepID"].ToString());
				}
				if(row["CPID"]!=null && row["CPID"].ToString()!="")
				{
					model.CPID=long.Parse(row["CPID"].ToString());
				}
				if(row["IsUse"]!=null && row["IsUse"].ToString()!="")
				{
					model.IsUse=int.Parse(row["IsUse"].ToString());
				}
				if(row["TPDepLevel"]!=null && row["TPDepLevel"].ToString()!="")
				{
					model.TPDepLevel=int.Parse(row["TPDepLevel"].ToString());
                }
                if (row["IsCheck"] != null && row["IsCheck"].ToString() != "")
                {
                    model.IsCheck = int.Parse(row["IsCheck"].ToString());
                }
                if (row["TPPID"] != null && row["TPPID"].ToString() != "")
                {
                    model.TPPID = int.Parse(row["TPPID"].ToString());
                }
                //if (row["TPSendDepID"] != null)
                //{
                //    model.TPSendDepID = row["TPSendDepID"].ToString();
                //}

                if (row["Remarks"] != null)
                {
                    model.Remarks = row["Remarks"].ToString();
                }
                if (row["TPDescribeContent"] != null)
                {
                    model.TPDescribeContent = row["TPDescribeContent"].ToString();
                }
                if (row["AnnexUrl"] != null)
                {
                    model.AnnexUrl = row["AnnexUrl"].ToString();
                }
                if (row["AnnexName"] != null)
                {
                    model.AnnexName = row["AnnexName"].ToString();
                }
            }
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select AutoID,TPID,TPName,TPDescribe,TPIsRelease,TPReleaseStart,TPReleaseEnd,TPDesignUserID,TPDesignUserName,TPAddUserID,TPAddUserName,TPAddDateTime,TPDepID,CPID,IsUse,TPDepLevel,IsCheck,TPPID,Remarks,TPDescribeContent,AnnexUrl,AnnexName ");//,TPSendDepID
            strSql.Append(" FROM TrainPlan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" AutoID,TPID,TPName,TPDescribe,TPIsRelease,TPReleaseStart,TPReleaseEnd,TPDesignUserID,TPDesignUserName,TPAddUserID,TPAddUserName,TPAddDateTime,TPDepID,CPID,IsUse,TPDepLevel,IsCheck,TPPID,Remarks,TPDescribeContent,AnnexUrl,AnnexName ");//,TPSendDepID
            strSql.Append(" FROM TrainPlan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM TrainPlan ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.AutoID desc");
			}
			strSql.Append(")AS Row, T.*  from TrainPlan T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex , out int rowCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.AutoID desc");
            }
            strSql.Append(")AS Row, T.*  from TrainPlan T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");

            rowCount = 0;
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
                if (ds.Tables.Count > 0)
                    if (ds.Tables[0].Rows.Count > 0)
                        rowCount = ds.Tables[0].Rows.Count;

            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            strSql.AppendFormat(" order by TPAddDateTime desc ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "TrainPlan";
			parameters[1].Value = "AutoID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        /// <summary>
        /// 是否存在该记录,名称是否重复
        /// </summary>
        public bool Exists(string TPNAME, DateTime? TPReleaseStart, DateTime? TPReleaseEnd)
        {
            DateTime dtStart = Convert.ToDateTime(TPReleaseStart);
            DateTime dtEnd = Convert.ToDateTime(TPReleaseEnd);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TrainPlan");
            strSql.Append(" where TPName=@TPName and TPReleaseStart='"+ dtStart.ToString("yyyy-MM-dd")+ "' and TPReleaseEnd='"+ dtEnd.ToString("yyyy-MM-dd")+"'");
            SqlParameter[] parameters = {
                    new SqlParameter("@TPName", SqlDbType.NVarChar,255)
            };
            parameters[0].Value = TPNAME;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 培训计划发布
        /// </summary>
        public bool Release(string TPIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update TrainPlan set TPIsRelease=1 ");
            strSql.Append(" where TPID in (" + TPIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetSendTrainPlanForDeptID(string deptid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from TrainPlan where TPID in (select TPID from TrainPlanSendStep where SendID in ( select TPSSID as SendID from TrainPlanSendDept where TPSDDeptID="+deptid+" )) and GETDATE()<TPReleaseEnd  and IsUse=1 and IsCheck=1 order by   TPDepLevel ASC ,TPAddDateTime desc  ");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
		/// 更新附件信息把上传分开
		/// </summary>
		public int UpdateAnnex(string TPID, string AnnexName,string AnnexUrl)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update TrainPlan set AnnexName='"+AnnexName+ "',AnnexUrl='"+AnnexUrl+"' where TPID="+TPID);
            
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        #endregion  ExtensionMethod
    }
}

